package model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dbhandler.*;

public class OpinionThatCounts {
	OpinionThatCountsUser user;

	/*
	 * Method  		  : loginValidate 
	 * Return 		  : integer 
	 * Argument 	  : String,String
	 * Responsibility : To validate a user login.					
	 */
	public int loginValidate(String username,String password)
	{
		int temp=0;
		String query = "select count(*) as total from login where username='"+username+"' and password='"+password+"'";
		DBHandler db = new DBHandler();
		db.getConnection();
		String count = db.executeSelect(query);
		temp=Integer.parseInt(count);
		db.closeConnection();
		return temp;
	}
	
	
	/*
	 * Method : view Bollywood Movie Details 
	 * Return : ArrayList<Movies>
	 * Argument : String Movie ID 
	 * Responsibility: To view Bollywood Movies Details
	 */
	public ArrayList<Movies> retrieveBollywoodMovies(int MovieId) {

		String query = "SELECT movie.movie_id," +
					"movie.movie_name," +
					"movie.movie_image," +
					"movie.user_id," +
					"movie.movie_description," +
					"movie.movie_like," +
					"movie.movie_dislike," +
					"movie.movie_rating," +
					"movie.movie_time," +
					"movie.movie_category_id FROM opinionthatcounts.movie";
		/*+
					"where movie.movie_category_id = "+MovieId+"";*/
		DBHandler db = new DBHandler();
		db.getConnection();
		ArrayList<Movies> conHist = db.retrieveMovieDetails(query);
		db.closeConnection();
		return conHist;
	}
	/*
	
	 * Method  		  : GetAccidentHistoryDetails 
	 * Return 		  : String[] 
	 * Argument 	  : String
	 * Responsibility : To get Accident History Details corresponding
	 * 					to selected proposalId.
	 
	public String[] getAccidentDetails(String pId)
	{
		String[] result = new String[2];
		String query = "select acc_hist_id from accident_history " +
				"where proposal_id =" + "'" + pId + "'";			
		DBHandler db = new DBHandler();
		db.getConnection();
		String accId = db.executeSelect(query);
		result[0] = accId;
		String query1 = "select count(*) from  accident_history ah, " +
						"proposal p where p.proposal_id = ah.proposal_id " +
						"and p.user_id = (select user_id from proposal " +
						"where proposal_id =" + "'" + pId  + "')";
		String count = db.executeSelect(query1);
		result[1] = count;
		return result;
		
		
	}
	
	 * Method  		  : AddAccidentHistoryDetails 
	 * Return 		  : integer 
	 * Argument 	  : Accident 
	 * Responsibility : To add Accident History Details corresponding
	 * 					to selected proposalId.
	 
	public int addAccident(Accident a) {
		String query1 = "select comm_sequence.NextVal from dual";
		DBHandler db = new DBHandler();
		db.getConnection();

		String accId = db.executeSelect(query1);
		accId = "acc_" + accId;
		String query = "insert into  accident_history ("
				+ "ACC_HIST_ID,PROPOSAL_ID,"
				+ "DATE_OF_ACCIDENT,NATURE_OF_ACCIDENT,COMMENTS) "
				+ "values ('" + accId + "','" + a.getProposalId() + "','"
				+ a.getDateOfAccident() + "','" + a.getNatureOfAccident()
				+ "','" + a.getComments() + "' )";

		int res = db.executeDML(query);
		db.closeConnection();
		return res;
	}

	
	 * Method 		  : deleteAccidentHistoryDetails 
	 * Return 		  : integer 
	 * Argument 	  : String[] accidentId 
	 * Responsibility : To delete Accident History Details
	 * 					corresponding to selected accidentIds
	 
	public int deleteAccidentHistoryDetails(String[] accId) {
		String s = "";
		for (int i = 0; i < ((accId.length) - 1); i++) {
			s = s + "'" + accId[i] + "', ";
		}
		s = s + "'" + accId[((accId.length) - 1)] + "'";
		String query = "delete from ACCIDENT_HISTORY where ACC_HIST_ID in" +
				"(" + s+ ")";
		DBHandler db = new DBHandler();
		db.getConnection();
		int i = db.executeDML(query);
		db.closeConnection();
		return i;
	}

	
	 * Method 		  : displayAccidentDetails (with the check box to delete 
	 * 					the selected accidentId details) 
	 * Return 		  : ArrayList<Accident> 
	 * Argument 	  : userId 
	 * Responsibility : To view Accident History Details corresponding to
	 * 			  		a particular userId
	 
	public ArrayList<Accident> displayAccidentDetails(String userId) {
		DBHandler db = new DBHandler();
		db.getConnection();
		String query;
		query = "select user_id  from ACCIDENT_HISTORY ah, PROPOSAL p where "
				+ "ah.PROPOSAL_ID = p.PROpOSAL_ID and p.user_id =" + userId;
		String rs1 = db.executeSelect(query);
		if (rs1 == null) {
			return null;
		} else {
			query = "select '' as VEH_ID,ah.ACC_HIST_ID,ah.PROPOSAL_ID,"
					+ "to_char(ah.DATE_OF_ACCIDENT)as DATE_OF_ACCIDENT,"
					+ "noa.NOA_TYPE as NATURE_OF_ACCIDENT,ah.COMMENTS "
					+ "from ACCIDENT_HISTORY ah, PROPOSAL p,NATURE_OF_ACCIDENT noa"
					+ " where ah.PROPOSAL_ID = p.PROPOSAL_ID "
					+ "and noa.NOA_ID=ah.NATURE_OF_ACCIDENT and p. USER_ID="
					+ userId;
			al = db.executeView(query);
			return al;
		}
	}

	
	 * Method 		  : updateAccidentDetails(with the radio button to select
	 * 					the details to be updated) 
	 * Return 		  : integer 
	 * Argument 	  : String accHistId,
	 * 					String dateOfAccident, 
	 * 					String natureOfAccident,
	 * 					String comments
	 * Responsibility: To update Accident History Details corresponding to a
	 * 				   particular accidentHistoryId
	 
	public int updateAccident(String accHistId, String dateOfAccident,
			String natureOfAccident, String comments) {
		String query = "update accident_history set NATURE_OF_ACCIDENT='"
				+ natureOfAccident + "', " + "DATE_OF_ACCIDENT='"
				+ dateOfAccident + "', COMMENTS='" + comments + "' "
				+ "where ACC_HIST_ID='" + accHistId + "'";
		DBHandler db = new DBHandler();
		db.getConnection();
		int res = db.executeDML(query);
		db.closeConnection();
		return res;
	}

	
	 * Method 		  : ViewAccident 
	 * Return 		  : ArrayList<Accident> 
	 * Argument 	  : String id
	 * Responsibility : To view Accident History Detail corresponding to a
	 * 					particular AccidentId
	 
	public ArrayList<Accident> viewAccident(String id) {
		String query = "select ah.ACC_HIST_ID,ah.PROPOSAL_ID,"
				+ "to_char(ah.DATE_OF_ACCIDENT)as DATE_OF_ACCIDENT,"
				+ "ah.NATURE_OF_ACCIDENT,ah.COMMENTS,v.veh_id "
				+ "from accident_history ah inner join"
				+ " proposal v on ah.proposal_id=v.proposal_id "
				+ "where ah.acc_hist_id='" + id + "' ";

		DBHandler db = new DBHandler();
		db.getConnection();
		al = db.executeView(query);
		db.closeConnection();
		return al;
	}

	
	 * Method 		  : ViewAccidentDetails 
	 * Return 		  : Accident object
	 * Argument 	  : accidentId 
	 * Responsibility : To view Accident History Detail corresponding
	 * 					to a particular AccidentId
	 
	public ArrayList<Accident> viewAccidentDetails(String accId, String userId){
		String accHisId = null, proposalId = null, doa = null,
			   natureOfAcc = null, comments = null;
		String query;
		if(accId.equals("all"))
			query = "select ah.ACC_HIST_ID,ah.PROPOSAL_ID,"
					+ "to_char(ah.DATE_OF_ACCIDENT)as DATE_OF_ACCIDENT,"
					+ "noa.NOA_TYPE as NATURE_OF_ACCIDENT,ah.COMMENTS "
					+ "from accident_history ah, NATURE_OF_ACCIDENT noa "
					+ "where ah.NATURE_OF_ACCIDENT=noa.NOA_ID "
					+ "and proposal_id in "
					+ "(select proposal_id from proposal where user_id ="+ "'"
					+ userId + "')";
		
		else {
			query = " select ah.ACC_HIST_ID,ah.PROPOSAL_ID,"
					+ "to_char(ah.DATE_OF_ACCIDENT)as DATE_OF_ACCIDENT,"
					+ "noa.NOA_TYPE as NATURE_OF_ACCIDENT,ah.COMMENTS "
					+ "from accident_history ah, NATURE_OF_ACCIDENT noa "
					+ "where ah.NATURE_OF_ACCIDENT=noa.NOA_ID "
					+ "and ACC_HIST_ID = '" + accId + "'";
		}

		DBHandler db = new DBHandler();
		db.getConnection();
		ResultSet rs = db.executeSelect1(query);
		try {
			while (rs.next()) {
				accHisId = rs.getString(1);
				proposalId = rs.getString(2);
				doa = rs.getString(3);
				natureOfAcc = rs.getString(4);
				comments = rs.getString(5);
				Accident a = new Accident("", accHisId, proposalId, doa,
						natureOfAcc, comments);
				al.add(a);
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return al;
	}

	
	 * Method : display AccidentHistoryId corresponding to a particular userId
	 * Return : ArrayList<String> 
	 * Argument : userId 
	 * Responsibility: To view AccidentHistoryId corresponding 
	 * 				   to a particular userId
	 
	
	
	public ArrayList<String> retrieveAccId(String userId) {
		String query ="select  ACC_HIST_ID from ACCIDENT_HISTORY ah, PROPOSAL p where ah.PROPOSAL_ID = p.PROPOSAL_ID AND p.USER_ID='"+userId+"'";
		DBHandler db = new DBHandler();
		db.getConnection();
		ArrayList<String> accId = db.retrieveConvId(query);
		db.closeConnection();
		return accId;
	}
	
	
	
	 * Method : AddConvictionHistory Details 
	 * Return : String 
	 * Argument : ConvictionHistory c 
	 * Responsibility: To add Conviction History Details
	 
	public int addConviction(ConvictionHistory c) throws SQLException {
		DBHandler db=new DBHandler();
		db.getConnection();
		int res=0;
		System.out.println(c.getDateOfConviction());
		String query1="select * from conviction_history where date_of_conviction='"+c.getDateOfConviction()+"' and user_id='"+c.getUserId()+"'";
		System.out.println(query1);
		ResultSet rs=db.executeSelect1(query1);
		if(rs.next()){
			System.out.println("details already exist");
		}
		else{
		String query="insert into conviction_history values('"+c.getConvictionHistoryId()+"','"+c.getUserId()+"','"+c.getDateOfConviction()+"','"+c.getNatureOfConviction()+"','"+c.getComments()+"')";
		System.out.println(query);
		res=db.executeDML_Con(query);
		}
		db.closeConnection();
		return res;
	}
	
	public String displayAddedConvictionId(String userid,String doc) 
	 {
	 String query="select con_id from conviction_history where user_id='"+userid+"' and date_of_conviction='"+doc+"'";
	 DBHandler db=new DBHandler();
	 db.getConnection();
	 String conid=db.executeDispConId(query);
	 db.closeConnection();
	 return conid;	 
	 }
	 
	
	 * Method : display Conviction history id 
	 * Return : String (conId) 
	 * Argument : userId 
	 * Responsibility: To view Conviction History id corresponding to a
	 * particular userId
	 
	public String getConvictionHistoryId(String userid) throws SQLException {
		String res = null;
		String query = "select con_id from conviction_history "
				+ "where user_id='" + userid + "'";
		DBHandler db = new DBHandler();
		db.getConnection();
		res = db.executeDispConId(query);
		db.closeConnection();
		return res;
	}

	
	 * Method : display Conviction history Details (with the check box to delete
	 * 			the selected convicitonId details) 
	 * Return : ArrayList<ConvictionHistory>
	 * Argument : userId 
	 * Responsibility: To view Conviction History Details corresponding 
	 * 					to a particular userId
	 
	public ArrayList<ConvictionHistory> displayConvictionDetails(String userid) {
		ArrayList<ConvictionHistory> ac = null;
		try {
			String query = "select ch.con_id," 
				+ "ch.user_id,"
				+ "to_char(ch.date_of_conviction,'dd-Mon-yyyy'),"
				+ "noc.noc_type as nature_of_conviction," 
				+ "ch.comments from conviction_history ch,nature_of_conviction noc"
				+ " where noc.noc_id=ch.nature_of_conviction and user_id='" + userid + "'";
			DBHandler db = new DBHandler();
			db.getConnection();
			ac = db.executeViewDML(query);
			db.closeConnection();
			return ac;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ac;
	}

	
	 * Method : delete Accident History Details 
	 * Return : integer 
	 * Argument :String[] convcitionId 
	 * Responsibility: To delete Conviction History Details corresponding
	 * 					to selected convcitionIds
	 
	public int deleteConvictionHistoryDetails(String[] convictionId)
			throws SQLException {
		int result = 0;
		String s = "";
		for (int i = 0; i < ((convictionId.length) - 1); i++) {
			s = s + "'" + convictionId[i] + "', ";
		}
		s = s + "'" + convictionId[((convictionId.length) - 1)] + "'";
		String query = "delete from conviction_history " + "where con_id in("
				+ s + ")";
		DBHandler db = new DBHandler();
		db.getConnection();
		result = db.executeDMLDelete(query);
		db.closeConnection();
		return result;
	}

	
	 * Method : display ConvictionHistoryId corresponding to a particular userId
	 * Return : ArrayList<String> 
	 * Argument : userId 
	 * Responsibility: To view ConvictionHistoryId corresponding 
	 * 				   to a particular userId
	 
	public ArrayList<String> retrieveConId(String userId) {
		String query = "select con_id from conviction_history "
				+ "where user_id='" + userId + "'";
		DBHandler db = new DBHandler();
		db.getConnection();
		ArrayList<String> conId = db.retrieveConvId(query);
		db.closeConnection();
		return conId;
	}

	
	 * Method : view Conviction Details 
	 * Return : ArrayList<ConvictionHistory>
	 * Argument : String userID 
	 * Responsibility: To view Conviction History Details corresponding to a particular userId
	 
	public ArrayList<ConvictionHistory> retrieveConvictionHistory(String userID) {

		String query = "select ch.con_id," 
						+ "ch.user_id,"
						+ "to_char(ch.date_of_conviction,'dd-Mon-yyyy'),"
						+ "noc.noc_type as nature_of_conviction," 
						+ "ch.comments from conviction_history ch,nature_of_conviction noc"
						+ " where noc.noc_id=ch.nature_of_conviction and user_id='"+userID+"'";
		DBHandler db = new DBHandler();
		db.getConnection();
		ArrayList<ConvictionHistory> conHist = db.executeDMLQuery(query);
		db.closeConnection();
		return conHist;
	}

	
	 * Method : view Conviction Detail of a particular convictionHistoryId
	 * Return : ConvictionHistory 
	 * Argument : String conHistID 
	 * Responsibility: To view Conviction History Detail corresponding to a particular
	 * convictionhistoryId
	 
	public ConvictionHistory retrieveConvictionHistoryOnId(String conID) {

		String query = "select ch.con_id," 
			+ "ch.user_id,"
			+ "to_char(ch.date_of_conviction,'dd-Mon-yyyy'),"
			+ "noc.noc_type as nature_of_conviction," 
			+ "ch.comments from conviction_history ch,nature_of_conviction noc"
			+ " where noc.noc_id=ch.nature_of_conviction and con_id='"+conID+"'";

		DBHandler db = new DBHandler();
		db.getConnection();
		ConvictionHistory conHist = db.executeDMLQueryOnId(query);
		db.closeConnection();
		return conHist;
	}

	
	 * Method : update Conviction Details (with the radio button to select the
	 * details to be updated) 
	 * Return : integer 
	 * Argument : String conId, String dateOfConviction, String natureOfConviction, String comments
	 * Responsibility: To update Conviction History Details corresponding to a
	 * particular convictionHistoryId
	 
	public int updateConvictionHistory(String conId, String dateOfConviction,
			String natureOfConviction, String comments) {
		String query = "update conviction_history set "
				+ "date_of_conviction='" + dateOfConviction + "',"
				+ "nature_of_conviction='" + natureOfConviction + "',"
				+ "comments='" + comments + "' where con_id='" + conId + "'";
		DBHandler db = new DBHandler();
		db.getConnection();
		int conHistUpdateResult = db.executeDMLUpdate(query);
		db.closeConnection();
		return conHistUpdateResult;
	}


	
	 * Method : getProposalDetails() 
	 * Returns : ArrayList of proposal details
	 * Arguments : none 
	 * Responsibility : to return the list of proposals against
	 * which a quote has not been generated.
	 
	public ArrayList<ProposalDetails> getProposalDetails() {
		ArrayList<ProposalDetails> pdtls = new ArrayList<ProposalDetails>();
		ResultSet rs = null;
		String query = "select * from proposal where flag=0";
		DBHandler dbc = new DBHandler();
		try {
			dbc.getConnection();
		} catch (Exception e2) {

			e2.printStackTrace();
		}
		try {
			rs = dbc.doSelect(query);
			while (rs.next()) {
				OpinionThatCountsUser user = new OpinionThatCountsUser(rs.getString("USER_ID"));
				pdtls.add(new ProposalDetails(rs.getString("PROPOSAL_ID"),
						user, rs.getString("VEH_ID"), rs.getString("flag"), rs
								.getDouble("CLAIM_AMOUNT")));
			}
		} catch (Exception e) {

			e.printStackTrace();
		}
		return (pdtls);
	}

	
	 * Method : selectProposal() 
	 * Returns : ProposalDetails 
	 * Arguments : pid
	 * Responsibility : to create and return the object of ProposalDetails asked
	 * by the employee
	 
	public ProposalDetails selectProposal(String pid) {
		ProposalDetails pdtls = null;
		ResultSet rs = null;
		String query = "select * from proposal where PROPOSAL_ID = '" + pid
				+ "' and flag=0";
		DBHandler dbc = new DBHandler();
		try {
			dbc.getConnection();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		try {
			rs = dbc.doSelect(query);
			while (rs.next()) {
				OpinionThatCountsUser user = new OpinionThatCountsUser(rs.getString("USER_ID"));
				pdtls = new ProposalDetails(rs.getString("PROPOSAL_ID"), user,
						rs.getString("VEH_ID"), rs.getString("flag"), rs
								.getDouble("CLAIM_AMOUNT"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return (pdtls);
	}

	
	 * Method : checkUser() 
	 * Returns : boolean 
	 * Arguments : uid 
	 * Responsibility : to check whether the user is present in the database 
	 * 					or not
	 
	public boolean isUserExist(String uid) {
		ResultSet rs = null;
		String query = "select * from fastquote_user where USER_ID = '" + uid
				+ "'";
		DBHandler dbc = new DBHandler();
		try {
			dbc.getConnection();
		} catch (Exception e2) {
			e2.printStackTrace();
		}

		try {
			rs = dbc.doSelect(query);
			if (rs.next()) {
				return (true);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	
	 * Method : checkProposal() 
	 * Returns : boolean 
	 * Arguments : pid 
	 * Responsibility : to check whether the proposal is present in the 
	 * 					database or not
	 
	public boolean checkProposal(String pid) {
		ResultSet rs = null;
		String query = "select * from proposal where PROPOSAL_ID = '" + pid
				+ "' ";
		DBHandler dbc = new DBHandler();
		try {
			dbc.getConnection();
		} catch (Exception e2) {

			e2.printStackTrace();
		}
		try {
			rs = dbc.doSelect(query);
			if (rs.next()) {
				System.out.println(rs.getString(1));
				return (true);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	
	 * Method : viewQuoteDetails() 
	 * Returns : ArrayList of QuoteDetails 
	 * Arguments : uid 
	 * Responsibility : to get the array list of the all the quotes for
	 * one particular user.
	 
	public ArrayList<QuoteDetails> viewQuoteDetails(String userId) {
		ResultSet rs = null;
		String query = "select p.proposal_id, q.quote_id, q.quote_amount, "
				+ "u.user_name from proposal p, fastquote_user u, "
				+ "quote q where p.proposal_id = q.proposal_id and "
				+ "p.user_id='" + userId + "' and p.user_id=u.user_id";
		DBHandler dbc = new DBHandler();
		try {
			dbc.getConnection();
		} catch (Exception e2) {

			e2.printStackTrace();
		}
		try {
			rs = dbc.doSelect(query);
			while (rs.next()) {
				ProposalDetails pdtls = new ProposalDetails(rs
						.getString("proposal_id"));
				qdtls.add(new QuoteDetails(rs.getString("quote_id"), pdtls, rs
						.getDouble("quote_amount")));
			}
		} catch (Exception e) {

			e.printStackTrace();
		}
		return qdtls;
	}

	
	 * Method : getQuoteDetails() 
	 * Returns : ArrayList of QuoteDetails 
	 * Arguments : quoteId 
	 * Responsibility : to get the array list of quote details for one
	 * particular quote.
	 
	public ArrayList<QuoteDetails> getQuoteDetails(String quoteId) {
		ResultSet rs = null;
		String query = "select * from quote where quote_id = '" + quoteId + "'";
		DBHandler dbc = new DBHandler();
		try {
			dbc.getConnection();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		try {
			rs = dbc.doSelect(query);
			while (rs.next()) {
				ProposalDetails pdtls = new ProposalDetails(rs
						.getString("proposal_id"));
				qdtls.add(new QuoteDetails(rs.getString("quote_id"), pdtls, rs
						.getDouble("quote_amount")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return qdtls;
	}

	
	 * Method : viewQuoteDetailsPid() 
	 * Returns : ArrayList of QuoteDetails
	 * Arguments : proposal id 
	 * Responsibility : to get the array list of the quotes for given proposal.
	 
	public ArrayList<QuoteDetails> viewQuoteDetailsPid(String proposalId) {
		ResultSet rs = null;
		String query = "select * from quote q " + "where proposal_id = '"
				+ proposalId + "'";
		DBHandler dbc = new DBHandler();
		try {
			dbc.getConnection();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		try {
			rs = dbc.doSelect(query);
			while (rs.next()) {
				ProposalDetails pdtls = new ProposalDetails(rs
						.getString("proposal_id"));
				qdtls.add(new QuoteDetails(rs.getString("quote_id"), pdtls, rs
						.getDouble("quote_amount")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return qdtls;
	}

	
	 * Method : deleteQuoteDetails() 
	 * Returns : int 
	 * Arguments : Array of quote id
	 * Responsibility : to delete the quote details for an array of quotes.
	 
	public int deleteQuoteDetails(String[] quoteId) {
		String s = "", pid = "";
		ResultSet rs;
		for (int i = 0; i < ((quoteId.length) - 1); i++) {
			s = s + "'" + quoteId[i] + "', ";
		}
		s = s + "'" + quoteId[((quoteId.length) - 1)] + "'";
		String query = "delete from quote where quote_id in(" + s + ")";
		String query1 = "select proposal_id from quote where quote_id " + "in("
				+ s + ")";
		DBHandler db = new DBHandler();
		db.getConnection();
		rs = db.doSelect(query1);
		try {
			while (rs.next()) {
				pid += rs.getString("proposal_id");
				// if(rs.isLast())
				// break;
				// pid+=",";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		boolean b = db.doDelete(query);
		if (b)
			return 0;
		else {
			query = "update proposal set flag=0 where proposal_id " + "in ('"
					+ pid + "')";
			db.doUpdate(query);
			return 1;
		}
	}

	
	 * Method : getProposal() 
	 * Returns : ProposalDetails 
	 * Arguments : proposal id
	 * Responsibility : to return the proposal details for a particular proposal
	 * id.
	 

	public ProposalDetails getProposal(String proposalId) {
		ResultSet rs = null;
		String query = "select * from proposal " + "where PROPOSAL_ID = '"
				+ proposalId + "'";
		DBHandler dbc = new DBHandler();
		ProposalDetails p = null;

		try {
			dbc.getConnection();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		try {
			rs = dbc.doSelect(query);
			while (rs.next()) {
				OpinionThatCountsUser user = new OpinionThatCountsUser(rs.getString("USER_ID"));
				p = new ProposalDetails(rs.getString("PROPOSAL_ID"), user, rs
						.getString("VEH_ID"), rs.getString("FLAG"), rs
						.getDouble("CLAIM_AMOUNT"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return p;

	}

	
	 * Method : generateQuote() 
	 * Returns : QuoteDetails 
	 * Arguments : proposal id
	 * Responsibility : to quote for a particular proposal with particular
	 * prposalId.
	 
	public QuoteDetails generateQuote(String proposalId) {
		ResultSet rs = null;
		String nat1 = null, quoteId = null;
		ArrayList<String> nat2 = new ArrayList<String>();
		QuoteDetails q = null;
		double ded = 0, ded1 = 0, ded2 = 0, claim = 0, quote = 0;
		String query1 = "select nature_of_accident from accident_history a "
				+ "where proposal_id='" + proposalId + "'";
		String query2 = "select nature_of_conviction from "
				+ "conviction_history c, proposal p "
				+ "where p.user_id=c.user_id and " + "p.proposal_id='"
				+ proposalId + "'";
		DBHandler dbc = new DBHandler();

		try {
			dbc.getConnection();

		} catch (Exception e2) {
			e2.printStackTrace();
		}
		try {
			rs = dbc.doSelect(query1);
			while (rs.next()) {
				nat1 = rs.getString("nature_of_accident");
			}
			if (nat1 == null) {// no accident details available for
				// the proposal.
				q = new QuoteDetails("-1");// constructed object just to
				// pass the error code.
				return q;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			rs = dbc.doSelect(query2);
			while (rs.next()) {
				nat2.add(rs.getString("nature_of_conviction"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		String query3 = "select noa_deduction_rate from nature_of_accident "
				+ "where noa_id='" + nat1 + "'";

		try {
			rs = dbc.doSelect(query3);
			while (rs.next()) {
				ded1 = rs.getDouble(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		for (String val : nat2) {
			String query4 = "select noc_deduction_rate from "
					+ "nature_of_conviction where noc_id='" + val + "'";
			try {
				rs = dbc.doSelect(query4);
				while (rs.next())
					ded2 += rs.getDouble("noc_deduction_rate");

			}

			catch (Exception e) {
				e.printStackTrace();
			}

		}

		String query5 = "select claim_amount from proposal "
				+ "where proposal_id='" + proposalId + "'";
		try {
			rs = dbc.doSelect(query5);
			while (rs.next()) {
				claim = rs.getDouble(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		ded = ((ded1 + ded2) * claim / 100);
		quote = claim - ded;
		if (quote > 0) {
			String query6 = "select comm_sequence.nextval from dual";
			rs = dbc.doSelect(query6);
			try {
				while (rs.next()) {
					quoteId = new Integer(rs.getInt(1)).toString();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			String query7 = "insert into quote values('QT_" + quoteId + "','"
					+ proposalId + "'," + quote + ")";
			ProposalDetails p = new ProposalDetails(proposalId);
			q = new QuoteDetails("QT_" + quoteId, p, quote);
			dbc.doInsert(query7);

			String query8 = "update proposal set flag=1 "
					+ "where proposal_id='" + proposalId + "'";
			dbc.doUpdate(query8);
			String query9 = "select * from quote " + "where quote_id='QT_||"
					+ quoteId + "'";
			rs = dbc.doSelect(query9);

		} else { // when total quote amount goes below zero.
			q = new QuoteDetails("-2");//
		}
		return q;
	}

	
	 * Method : checkFlag() 
	 * Returns : flag(int) of particular proposal.
	 * Arguments : proposalId 
	 * Responsibility : to return the flag(int) of particular proposal,whether the quote for this proposal has been
	 * generated or not.
	 

	public int checkFlag(String proposalId) {
		int i = 0;
		ResultSet rs = null;
		String query = "select flag from proposal where PROPOSAL_ID = '"
				+ proposalId + "'";
		DBHandler dbc = new DBHandler();

		try {
			dbc.getConnection();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		try {
			rs = dbc.doSelect(query);
			while (rs.next()) {
				i = rs.getInt("flag");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return i;
	}
	
	 * Method : rejectProposal() 
	 * Returns : boolean value.
	 * Arguments : proposalId 
	 * Responsibility : to return the flag,whether the proposal has been successfully
	 * deleted or not.
	 

public boolean rejectProposal(String pid) {
		
		int rs=0;
		
		String query = "delete from proposal where PROPOSAL_ID = '" + pid
				+ "'";
		System.out.println(query);
		DBHandler dbc = new DBHandler();
		try {
			dbc.getConnection();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		try {
			rs = dbc.executeDML(query);
			}
		 catch (Exception e) {
			e.printStackTrace();
		}
		query="delete from accident_history where PROPOSAL_ID = '" + pid
				+ "'";
		System.out.println(query);
		try {
			dbc.getConnection();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		try {
			dbc.executeDML(query);
			
			}
		 catch (Exception e) {
			e.printStackTrace();
		}
			if(rs!=0){
					
					return true;
				}	
		return false;
	}

public ArrayList<String> retrieveProposalId(String userId) 
{
	String query ="select proposal_id from proposal where user_id='"+userId+"' and proposal_id not in (select proposal_id from accident_history)";
	System.out.println(query);
	DBHandler db = new DBHandler();
	db.getConnection();
	ArrayList<String> proposalId = db.retrieveConvId(query);
	db.closeConnection();
	return proposalId;
}
*/
}
